﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Reflection;
using System.Collections;


namespace SqlHelper
{
    public class SQLHELP
    {
        public static String ConnectionString = ConfigurationManager.ConnectionStrings["SqlConnection"].ToString();

        public SQLHELP()
        {
 
        }

        public static DataTable GetDataTableNoParm(String sql)
        {
            DataSet ds = new DataSet();
            using(SqlConnection conn = new SqlConnection (ConnectionString))
            {
                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    da.Fill(ds);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return ds.Tables[0];
        }

        public static DataTable GetDataTableWithParm(String sql, SqlParameter[] parm)
        {
            DataSet ds = new DataSet();
            using(SqlConnection conn = new SqlConnection (ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand ())
                {
                    PrepareCommand(cmd, conn, null, sql, parm);
                    using(SqlDataAdapter da = new SqlDataAdapter (cmd))
                    {
                        try
                        {
                            da.Fill(ds);
                            cmd.Parameters.Clear();
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message);
                        }
                    }
                }
            }
            return ds.Tables[0];
        }

        public static int GetNonQueryNoParm(String sql)
        {
            int ResultNum = 0;
            using (SqlConnection conn = new SqlConnection (ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand (sql,conn))
                {
                    try
                    {
                        conn.Open();
                        ResultNum = cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

            }
            return ResultNum;
        }

        public static int GetNonQueryWithParm(String sql,SqlParameter[] parm)
        { 
            int ResultNum = 0;
            using (SqlConnection conn = new SqlConnection (ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand ())
                {
                    try
                    {
                        conn.Open();
                        PrepareCommand(cmd, conn, null, sql, parm);
                        ResultNum = cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally 
                    {
                        conn.Close();
                    }
                }
            }

            return ResultNum;
        }

        public static int GetCountNoParm(String sql)
        {
            return Convert.ToInt32(GetDataTableNoParm(sql).Rows[0][0].ToString());
        }

        public static int GetCountFromTable(String sql, SqlParameter[] parm)
        {
            return Convert.ToInt32(GetDataTableWithParm(sql,parm).Rows[0][0].ToString());
        }

        public static int GetSingleNoParm(String sql)
        {
            int ResultNum = 0;
            using(SqlConnection conn = new SqlConnection (ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        ResultNum = Convert.ToInt32(cmd.ExecuteScalar());
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally 
                    {
                        conn.Close();
                    }
                }
            }
            return ResultNum;
        }

        public static int GetSingleWithParm(String sql,SqlParameter[] parm)
        {
            int ResultNum = 0;
            using (SqlConnection conn = new SqlConnection (ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, sql, parm);
                        conn.Open();
                        ResultNum = Convert.ToInt32(cmd.ExecuteScalar());
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return ResultNum;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        public static DataTable getdiyerTable(int pageSize, int index,String sql,String TableName)
        {
            SqlDataAdapter dr = new SqlDataAdapter(sql, ConnectionString);
            DataSet ds = new DataSet();

            dr.Fill(ds, pageSize * (index - 1), pageSize, TableName);

            return ds.Tables[TableName];
        }
    }
}
